# import necessary libraries
import pandas as pd
import numpy as np
import holoviews as hv
import hvplot.pandas
import panel as pn
import plotly.express as px
from bokeh.models.formatters import NumeralTickFormatter
formatter = NumeralTickFormatter(format="0,0")
pn.extension('plotly')
hv.extension('bokeh')
orbis_scot_mfg_df = pd.read_pickle('Alex_work/data/orbis_data_with_postcode.pkl')
orbis_scot_mfg_df
| Company | Size | Postcode | Employees_Last_Reported | Employees_%Change | TO_Last_Reported | TO_2021 | TO_2020 | TO_%Change | TO_%Change_2021 | ... | NHS_HA_code | Admin_county_code | Admin_district_code | Admin_ward_code | longitude | latitude | Employees_Last_Reported_Scaled | TO_Last_Reported_Scaled | productivity | productivity_Scaled | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | LIFE TECHNOLOGIES LIMITED | Large | PA49RF | 1212.0 | 8.0 | 2698116.0 | NaN | 2698116.0 | 126.0 | NaN | ... | S08000031 | NaN | S12000038 | S13003086 | -4.445364 | 55.879283 | 0.109952 | 1.000000 | 2226.168317 | 0.254057 |
| 1 | WEIR GROUP PLC (THE) | Large | G21RW | NaN | NaN | 1978100.0 | 1978100.0 | 2286500.0 | -13.0 | -13.0 | ... | S08000031 | NaN | S12000049 | S13002976 | -4.255089 | 55.863100 | 0.000000 | 0.733141 | NaN | 0.000000 |
| 2 | DIAGEO SCOTLAND LIMITED | Large | EH129HA | 2944.0 | -6.0 | 1814000.0 | 1814000.0 | 1424000.0 | 27.0 | 27.0 | ... | S08000024 | NaN | S12000036 | S13002921 | -3.313175 | 55.931966 | 0.267078 | 0.672321 | 616.168478 | 0.070319 |
| 3 | HEINEKEN UK LIMITED | Large | EH129JZ | 2368.0 | -2.0 | 1098000.0 | NaN | 1098000.0 | -24.0 | NaN | ... | S08000024 | NaN | S12000036 | S13002921 | -3.299746 | 55.931991 | 0.214824 | 0.406951 | 463.682432 | 0.052917 |
| 4 | CIRRUS LOGIC INTERNATIONAL (UK) LTD | Large | EH39EG | 112.0 | -10.0 | 981397.0 | NaN | 981397.0 | -4.0 | NaN | ... | S08000024 | NaN | S12000036 | S13002929 | -3.194934 | 55.943943 | 0.020000 | 0.363734 | 8762.473214 | 1.000000 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2709 | VALHALLA CNC LTD | Micro | EH477AL | NaN | NaN | 0.0 | 0.0 | NaN | NaN | NaN | ... | S08000024 | NaN | S12000040 | S13002826 | -3.595575 | 55.876251 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2710 | SILVA RERUM LTD | Micro | EH260FP | NaN | NaN | 0.0 | 0.0 | NaN | NaN | NaN | ... | S08000024 | NaN | S12000019 | S13003021 | -3.220973 | 55.843804 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2711 | MAUCHIT LTD | Micro | PH27AU | NaN | NaN | 0.0 | NaN | 0.0 | -80.0 | NaN | ... | S08000030 | NaN | S12000048 | S13003063 | -3.186821 | 56.417503 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2712 | LUNE HANDMADE CRAFTS LIMITED | Micro | EH39NP | NaN | NaN | 0.0 | NaN | 0.0 | NaN | NaN | ... | S08000024 | NaN | S12000036 | S13002929 | -3.209031 | 55.940553 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2713 | WHITE THISTLE PRINTING LTD | Micro | EH153EG | 1.0 | NaN | 0.0 | 0.0 | NaN | NaN | NaN | ... | S08000024 | NaN | S12000036 | S13002935 | -3.114357 | 55.935902 | 0.020000 | 0.020000 | 0.000000 | 0.020000 |
2714 rows × 38 columns
idx=[]
for i in range (len(orbis_scot_mfg_df)):
postcodes = str(orbis_scot_mfg_df['Postcode'].values[i]).split(',')
selected_postcode = [item for item in postcodes if item.startswith('PH19') or (item.startswith('PH20')and item[4].isdigit()) or
item.startswith('PH22') or item.startswith('PH23') or item.startswith('PH24') or item.startswith('PH25') or
(item.startswith('PH26')and item[4].isdigit()) or item.startswith('PH30') or (item.startswith('PH31')and item[4].isdigit()) or
item.startswith('PH32') or item.startswith('PH33') or item.startswith('PH34') or item.startswith('PH35') or
item.startswith('PH36') or item.startswith('PH37') or item.startswith('PH38') or item.startswith('PH39') or
item.startswith('PH40') or (item.startswith('PH41')and item[4].isdigit()) or
item.startswith('PH42') or item.startswith('PH43') or item.startswith('PH44') or
item.startswith('IV') or
item.startswith('KW') or item.startswith('HS') or item.startswith('ZE')]
if len(selected_postcode)==0:
idx.append(i)
else:
orbis_scot_mfg_df['Postcode'].values[i] = selected_postcode[0]
orbis_scot_mfg_df.drop(orbis_scot_mfg_df.index[idx], axis=0, inplace=True)
highlands_mfg_df = orbis_scot_mfg_df
highlands_mfg_df
| Company | Size | Postcode | Employees_Last_Reported | Employees_%Change | TO_Last_Reported | TO_2021 | TO_2020 | TO_%Change | TO_%Change_2021 | ... | NHS_HA_code | Admin_county_code | Admin_district_code | Admin_ward_code | longitude | latitude | Employees_Last_Reported_Scaled | TO_Last_Reported_Scaled | productivity | productivity_Scaled | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 49 | GLOBAL ENERGY (GROUP) LIMITED | Large | IV11SN | 429.0 | -27.0 | 118849.0 | NaN | 118849.0 | -29.0 | NaN | ... | S08000022 | NaN | S12000017 | S13003005 | -4.223005 | 57.488285 | 0.038919 | 0.044049 | 277.037296 | 0.031616 |
| 72 | ALVANCE BRITISH ALUMINIUM LTD | Large | PH336TH | 104.0 | 14.0 | 93500.0 | NaN | NaN | 9.0 | NaN | ... | S08000022 | NaN | S12000017 | S13003010 | -5.072608 | 56.829076 | 0.020000 | 0.034654 | 899.038462 | 0.102601 |
| 77 | ALPHA SOLWAY LIMITED | Large | ZE10EB | 315.0 | 320.0 | 88476.0 | 88476.0 | 16928.0 | 423.0 | 423.0 | ... | S08000026 | NaN | S12000027 | S13002777 | -1.143854 | 60.152931 | 0.028577 | 0.032792 | 280.876190 | 0.032054 |
| 82 | ROSS-SHIRE ENGINEERING LIMITED | Large | IV67UA | 834.0 | 12.0 | 81879.0 | NaN | 81879.0 | 8.0 | NaN | ... | S08000022 | NaN | S12000017 | S13002997 | -4.453243 | 57.507271 | 0.075660 | 0.030347 | 98.176259 | 0.020000 |
| 92 | AQUASCOT LIMITED | Large | IV170PJ | 188.0 | 5.0 | 69385.0 | NaN | 69385.0 | 7.0 | NaN | ... | S08000022 | NaN | S12000017 | S13002995 | -4.269383 | 57.690906 | 0.020000 | 0.025716 | 369.069149 | 0.042119 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2626 | SCOTTISH TIMBER QUAICHS | Micro | IV243DJ | 1.0 | NaN | 41.0 | NaN | 41.0 | NaN | NaN | ... | S08000022 | NaN | S12000017 | S13002990 | -4.352697 | 57.871167 | 0.020000 | 0.020000 | 41.000000 | 0.020000 |
| 2633 | MASTER TAILORS (DINGWALL) LIMITED | Micro | IV11DR | NaN | NaN | 40.0 | NaN | 40.0 | NaN | NaN | ... | S08000022 | NaN | S12000017 | S13003003 | -4.226869 | 57.478585 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2685 | HEBRIDEAN PRESERVES AND PASTRIES LTD. | Micro | HS95UA | NaN | NaN | 3.0 | NaN | 3.0 | -44.0 | NaN | ... | S08000028 | NaN | S12000013 | S13002600 | -7.441370 | 56.963055 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2687 | RADDERY EQUINE LIMITED | Micro | IV108SN | NaN | NaN | 3.0 | NaN | 3.0 | -40.0 | NaN | ... | S08000022 | NaN | S12000017 | S13002998 | -4.152225 | 57.603004 | 0.000000 | 0.020000 | NaN | 0.000000 |
| 2708 | GOODIE GUTS LTD | Micro | PH337EW | 1.0 | NaN | 0.0 | 0.0 | NaN | NaN | NaN | ... | S08000022 | NaN | S12000017 | S13003000 | -5.100599 | 56.838639 | 0.020000 | 0.020000 | 0.000000 | 0.020000 |
253 rows × 38 columns
s='''
red, yellow, orange, aqua, magenta,
blue, brown, cadetblue,
chartreuse, chocolate,
darkcyan,
darkgoldenrod, darkgray, darkgreen, darkmagenta, darkolivegreen, darkorange,
darkorchid, darkred, darksalmon, darkseagreen,
darkslateblue, darkslategray, darkslategrey,
darkturquoise, darkviolet, deeppink, deepskyblue,
dimgray, dimgrey, dodgerblue, firebrick,
floralwhite, forestgreen, fuchsia, gainsboro,
ghostwhite, gold, goldenrod, gray, grey, green,
greenyellow, honeydew, hotpink, indianred, indigo,
ivory, khaki, lavender, lavenderblush, lawngreen,
lemonchiffon, lightblue, lightcoral, lightcyan,
lightgoldenrodyellow, lightgray, lightgrey,
lightgreen, lightpink, lightsalmon, lightseagreen,
lightskyblue, lightslategray, lightslategrey,
lightsteelblue, lightyellow, lime, limegreen,
linen, maroon, mediumaquamarine,
mediumblue, mediumorchid, mediumpurple,
mediumseagreen, mediumslateblue, mediumspringgreen,
mediumturquoise, mediumvioletred, midnightblue,
mintcream, mistyrose, moccasin, navajowhite, navy,
oldlace, olive, olivedrab, orangered,
orchid, palegoldenrod, palegreen, paleturquoise,
palevioletred, papayawhip, peachpuff, peru, pink,
plum, powderblue, purple, rosybrown,
royalblue, saddlebrown, salmon, sandybrown,
seagreen, seashell, sienna, silver, skyblue,
slateblue, slategray, slategrey, snow, springgreen,
steelblue, tan, teal, thistle, tomato, turquoise,
violet, wheat, white, whitesmoke,
yellowgreen, aliceblue, antiquewhite, beige, bisque, black, blanchedalmond, coral, darkblue, cornsilk, burlywood, darkgrey, cyan, cornflowerblue, aquamarine, crimson,
darkkhaki,
blueviolet
'''
colours = s.split(',')
colours = [c.replace('\n','') for c in colours]
colours = [c.replace(' ','') for c in colours]
colour_map = {bvd:c for bvd,c in zip(set(highlands_mfg_df['BvD']), colours)}
colour_map
{'Printing & Publishing': 'red',
'Miscellaneous Manufacturing': 'yellow',
'Transport Manufacturing': 'orange',
'Food & Tobacco Manufacturing': 'aqua',
'Textiles & Clothing Manufacturing': 'magenta',
'Industrial, Electric & Electronic Machinery': 'blue',
'Wood, Furniture & Paper Manufacturing': 'brown',
'Leather, Stone, Clay & Glass products': 'cadetblue',
'Metals & Metal Products': 'chartreuse',
'Chemicals, Petroleum, Rubber & Plastic': 'chocolate'}
rev_geog_pane = pn.pane.Markdown("""
# Revs by Location
""", width=200)
rev_geog_fig = px.scatter_mapbox(highlands_mfg_df, lat="latitude", lon="longitude", color="BvD", color_discrete_map=colour_map, zoom=6, mapbox_style='open-street-map',
hover_name='Company', hover_data=['Employees_Last_Reported','TO_Last_Reported','Postcode'], height=1200, width=1800, size='TO_Last_Reported_Scaled', size_max = 10)
rev_geog_fig.update_layout(legend = dict(font = dict(family = "Courier", size = 25, color = "black")),
legend_title = dict(font = dict(family = "Courier", size = 25, color = "blue")))
rev_geog_fig_pane = pn.pane.Plotly(rev_geog_fig)
rev_geog_layout = pn.Column(rev_geog_pane, rev_geog_fig_pane)
rev_geog_layout.servable()
# Create widget for the panel
radio_buttons = pn.widgets.RadioButtonGroup(options=['Employees', 'Turnover'])
mfg_df_start = highlands_mfg_df.copy()
mfg_df_start.set_index("BvD",drop=True, append=False, inplace=True)
# Create panel function for radio buttons and checkboxes for SME and stacked individual company details
@pn.depends(radio_buttons)
def counts_emp_turnover(x, width = 1000, height = 1000, show_stacked = False, SME = False):
# Defining employess plots
if x == 'Employees':
if SME:
mfg_df = mfg_df_start[(mfg_df_start['Size'] != 'Large')]
if show_stacked:
c2_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"Employees_Last_Reported": "sum"})
c2_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
c2_df = c2_df.sort_values(by='Total employees', ascending = False)
c2_a = c2_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
return(c2_a)
else:
c3_df = mfg_df.groupby(mfg_df.index).agg({"Employees_Last_Reported": "sum"})
c3_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
c3_df = c3_df.sort_values(by='Total employees', ascending = False)
c3_a = c3_df[['Total employees']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
return(c3_a)
else:
mfg_df = mfg_df_start
if show_stacked:
c4_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"Employees_Last_Reported": "sum"})
c4_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
c4_df = c4_df.sort_values(by='Total employees', ascending = False)
c4_a = c4_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
return(c4_a)
else:
c5_df = mfg_df.groupby(mfg_df.index).agg({"Employees_Last_Reported": "sum"})
c5_df.rename(columns = {"Employees_Last_Reported":"Total employees"}, inplace = True)
c5_df = c5_df.sort_values(by='Total employees', ascending = False)
c5_a = c5_df[['Total employees']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
return(c5_a)
# Defining turnover plots
if x== 'Turnover':
if SME:
mfg_df = mfg_df_start[(mfg_df_start['Size'] != 'Large')]
if show_stacked:
c6_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"TO_Last_Reported": "sum"})
c6_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
c6_df = c6_df.sort_values(by='Total turnover', ascending = False)
c6_a = c6_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
return(c6_a)
else:
c7_df = mfg_df.groupby(mfg_df.index).agg({"TO_Last_Reported": "sum"})
c7_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
c7_df = c7_df.sort_values(by='Total turnover', ascending = False)
c7_a = c7_df[['Total turnover']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
return(c7_a)
else:
mfg_df = mfg_df_start
if show_stacked:
c8_df = mfg_df.groupby([mfg_df.index,'Company']).agg({"TO_Last_Reported": "sum"})
c8_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
c8_df = c8_df.sort_values(by='Total turnover', ascending = False)
c8_a = c8_df.hvplot.bar(height=height, width=width, stacked=True, rot=75,yformatter=formatter).opts(fontscale=1.0).options(fontscale=1.0, framewise=True, axiswise=True, yaxis='left', show_grid=True)
return(c8_a)
else:
c9_df = mfg_df.groupby(mfg_df.index).agg({"TO_Last_Reported": "sum"})
c9_df.rename(columns = {"TO_Last_Reported":"Total turnover"}, inplace = True)
c9_df = c9_df.sort_values(by='Total turnover', ascending = False)
c9_a = c9_df[['Total turnover']].hvplot.bar(height=height, width=width, rot=75, yformatter=formatter).options(fontscale=1.0, framewise=True, yaxis='left', show_grid=True)
return(c9_a)
# Plot total number of employess and turnover for each sector(depending on SIC code)
emp_db_explanation_pane = pn.pane.Markdown("""
# How many employees and how much turnover?
""", width=200)
# Create interactive panels with radio buttons and checkboxes
emp_db_chart_interact = pn.interact(counts_emp_turnover, x=radio_buttons)
emp_db_layout = pn.Row(emp_db_explanation_pane, emp_db_chart_interact)
emp_db_layout.servable()